<?php

class Business_detailController extends CController
{
	const PAGE_SIZE=10;

	/**
	 * @var string specifies the default action to be 'list'.
	 */
	public $defaultAction='create';

	/**
	 * @var CActiveRecord the currently loaded data model instance.
	 */
	private $_model;

	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			array('application.filters.LoginFilter'),
			array('application.filters.AuthFilter'),
		);
	}
	
	/**
	 * Shows a particular model.
	 */
	public function actionShow()
	{
		$this->render('show',array('model'=>$this->loadBusiness_detail()));
	}

	/**
	 * Creates a new model.
	 * If creation is successful, the browser will be redirected to the 'show' page.
	 */
	public function actionCreate()
	{
		$model=new Business_detail;
		if(isset($_POST['Business_detail']))
		{
			$model->attributes=$_POST['Business_detail'];
			if($model->save()){
				$this->redirect(array('show','id'=>$model->id,'ret'=>'create','ok'=>'0'));
			}	
		}
		$this->render('create',array('model'=>$model));
	}

	/**
	 * Updates a particular model.
	 * If update is successful, the browser will be redirected to the 'show' page.
	 */
	public function actionUpdate()
	{
		$model=$this->loadBusiness_detail();
		if(isset($_POST['Business_detail']))
		{
			$model->attributes=$_POST['Business_detail'];
			if($model->save()){
				$this->redirect(array('show','id'=>$model->id,'ret'=>'admin','ok'=>'1')+$_GET);  
			}
		}
		$this->render('update',array('model'=>$model));
	}

	/**
	 * Deletes a particular model.
	 * If deletion is successful, the browser will be redirected to the 'list' page.
	 */
	public function actionDelete()
	{
		if(isset($_GET['id'])){
			Business_detail::model()->updateByPk($_GET['id'],array('state'=>1));
			unset($_GET['id']);
			$this->redirect(array('admin')+$_GET);
		}
		else
			throw new CHttpException(400,'无法获得要删除的信息ID!');
	}

	/**
	 * Manages all models.
	 */
	public function actionAdmin()
	{	
		$this->search(false);
	}
	
	public function actionSearch(){
		$this->search();
	}
	
	private function search($method=true){
		$criteria=new CDbCriteria();
		$criteria->join='INNER JOIN customer_name ON business_detail.fk_customer_id=customer_name.id';
		$criteria->select='business_detail.*,customer_name.name as customer_name';
		$criteria->addCondition('business_detail.state=0');
		$blur=true;
		if(Yii::app()->getRequest()->isPostRequest){
			if(isset($_POST['keys'])){//页面传来的搜索关键字数组
				$_GET['keys']=$_POST['keys'];
			}
		}
		if(isset($_GET['keys'])){
			if(isset($_GET['keys']['blur'])&&$_GET['keys']['blur']=='0') $blur=false;
			foreach($_GET['keys'] as $key=>$val){
				if(trim($val)=='')
					unset($_GET['keys'][$key]);
				else if($key=='s_date'){//时间查询
					$criteria->addCondition("occur_time>='{$val}'");
				}
				else if($key=='e_date'){//时间查询  
					$criteria->addCondition("occur_time<'".date('Y-m-d',strtotime($val." +1days"))."'");
				}
				else if($key!='blur'){//按照模糊或精确查询
					$blur?$criteria->addSearchCondition($key,$val):$criteria->addCondition("{$key}='{$val}'");
				}
			}
		}
		$model=Business_detail::model();
		$pages=new CPagination($model->count($criteria));
		$pages->pageSize=Yii::app()->params['page_size']['business_detail'];
		$pages->applyLimit($criteria);
		
		$sort=new CSort('Business_detail');
		$sort->defaultOrder='occur_time desc';
		$sort->attributes=array_merge($model->attributeNames(),array('customer_name'));
		$sort->applyOrder($criteria);

		$models=$model->findAll($criteria);
		
		//如果最后一页加合计
		if(($pages->getCurrentPage()+1)==$pages->getPageCount()){
			$attr_names=$model->excelAttributes();
			$sql="'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;合&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
			&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;计' as customer_name";
			foreach($attr_names as $attr=>$val){
				if(isset($val['sum'])){
					if(($val['sum'])==1)
						$sql.=",sum($attr) as $attr";
					else
						$sql.=",".$val['sum'].' as '.$attr;
				}
			}
			$criteria->select=$sql;
			$criteria->order='';
			$criteria->limit=1;$criteria->offset=-1;
			$sum_model=$model->find($criteria);
			$models[]=$sum_model;
		}
		$this->render('admin',array(
			'models'=>$models,
			'pages'=>$pages,
			'sort'=>$sort,
			'search'=>$method,
		));
	}

	/**
	 * Returns the data model based on the primary key given in the GET variable.
	 * If the data model is not found, an HTTP exception will be raised.
	 * @param integer the primary key value. Defaults to null, meaning using the 'id' GET variable
	 */
	public function loadBusiness_detail($id=null)
	{
		if($this->_model===null)
		{
			if($id!==null || isset($_GET['id'])){
				$criteria=new CDbCriteria();
				$criteria->join='INNER JOIN customer_name ON business_detail.fk_customer_id=customer_name.id';
				$criteria->select='business_detail.*,customer_name.name as customer_name';
				$criteria->addCondition('business_detail.id='.($id!==null ? $id : $_GET['id']));
				$this->_model=Business_detail::model()->find($criteria);
			}
			if($this->_model===null)
				throw new CHttpException(404,'The requested page does not exist.');
		}
		return $this->_model;
	}
	
	//月统计
	public function actionMonthly_statistics(){
		if(Yii::app()->request->isPostRequest){
			if(trim($_POST['s_date'])=='') $_POST['s_date']=date('Y-m');
			$model=Business_detail::model();
			if($_POST['b_type']=='0'){//运输 月汇总
				$sql="select sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,carrier_type
					from business_detail
					where state=0 and business_type=0 and date_format(occur_time,'%Y-%m')='{$_POST['s_date']}'
					group by carrier_type
					order by carrier_type desc";
				$sum_models=$model->findAllBySql($sql);//各承运方运输合计
				if(count($sum_models)==0){//如果承运为空
					if(isset($_GET['excel']))//输出excel
						$this->redirect(array('site/to_close','message'=>'4'));
					else
						$this->render('transport_statistics_month');
					return;
				}
				elseif(count($sum_models)==1){
					if($sum_models[0]->carrier_type==1)
						$sum_models[1]=new Business_detail;
					else{
						$sum_models[1]=$sum_models[0];
						$sum_models[0]=new Business_detail;
					}	
				}
				$sql="select sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,customer_name.name as customer_name,carrier_type
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type=0 and date_format(occur_time,'%Y-%m')='{$_POST['s_date']}' and business_detail.state=0
					GROUP BY fk_customer_id,carrier_type
					order by carrier_type desc,occur_time desc";
				$models=$model->findAllBySql($sql);
				if(isset($_GET['excel']))//输出excel
					$this->toTransport_statistics_Excel($models,$sum_models);
				else	
					$this->render('transport_statistics_month',array('models'=>$models,'sum_models'=>$sum_models));
				return;
			}
			else{//仓储或装卸 月统计
				$sql="select sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount
					from business_detail
					where state=0 and business_type={$_POST['b_type']} and date_format(occur_time,'%Y-%m')='{$_POST['s_date']}'";
				$sum_model=$model->findBySql($sql);//仓储或装卸  合计
				$sql="select sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,customer_name.name as customer_name,carrier_type
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type={$_POST['b_type']} and date_format(occur_time,'%Y-%m')='{$_POST['s_date']}' and business_detail.state=0
					GROUP BY fk_customer_id
					order by occur_time desc";
				$models=$model->findAllBySql($sql);
				if(empty($models)){//如果仓储或装卸为空
					if(isset($_GET['excel']))//输出excel
						$this->redirect(array('site/to_close','message'=>'4'));
					else
						$this->render('storage_statistics_month');
					return;
				}
				if(isset($_GET['excel']))//输出excel
					$this->toStorage_statistics_Excel($models,$sum_model);
				else	
					$this->render('storage_statistics_month',array('models'=>$models,'sum_model'=>$sum_model));
				return;
			}
		}
		$this->render('transport_statistics_month');
	}
	
	//月运输统计输出excel
	private function toTransport_statistics_Excel($models,$sum_models){
		
		$model=$models[0];
		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name="{$_POST['s_date']}运输配送业务报表";
				
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		//所有单元格添加边框
		$sum=count($models);
		$sharedStyle = new PHPExcel_Style();
		$sharedStyle->applyFromArray(
		array('borders' => array(
			'left'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
			)
		 ));
		$l=3;
		if($models[0]->carrier_type==1) $l++;
		if($models[$sum-1]->carrier_type==0) $l++;
		$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle, "A1:j".($sum+$l));
		
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		//第三方运输遍历
		foreach($models as $n=>$model){
			if($model->carrier_type==0) break;
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
			foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if($attr=='disparity_sum') $value=sprintf('%.2f',$model->disparity/$sum_models[0]->disparity*100).'%';
				$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
			}
			if($n%2==1)
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFefefef');
			$row_index++;
		}
		//第三方小计
		if($models[0]->carrier_type==1){
			$model=$sum_models[0];
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
			foreach($excel_names as $attr=>$val){
				$value=$model->$attr;
				if($attr=='disparity_sum') $value=sprintf('%.2f',$model->disparity/$sum_models[0]->disparity*100).'%';
				$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
			}
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($row_index),'第三方小计');
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFB7DDF2');
			$row_index++;
		}
		//自有车运输遍历
		foreach($models as $n=>$model){
			if($model->carrier_type==1) continue;
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
			foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if($attr=='disparity_sum') $value=sprintf('%.2f',$model->disparity/$sum_models[1]->disparity*100).'%';
				$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
			}
			if($n%2==1)
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFefefef');
			$row_index++;
		}
		//自有车小计
		if($models[count($models)-1]->carrier_type==0){
			$model=$sum_models[1];
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
			foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if($attr=='disparity_sum') $value=sprintf('%.2f',$model->disparity/$sum_models[1]->disparity*100).'%';
				$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
			}
			$objPHPExcel->getActiveSheet()->setCellValue('B'.($row_index),'自有车小计');
			$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFB7DDF2');
			$row_index++;
		}
		//总计
		$model=$sum_models[0];
		$model1=$sum_models[1];
		$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
		foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
			$value=$model->$attr+$model1->$attr;
			if($attr=='disparity_sum') $value='100%';
			else if($attr=='disparity_ratio') $value=sprintf('%.2f',($model->disparity+$model1->disparity)/($model->receivable_amount+$model1->receivable_amount)*100).'%';
			$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
		}
		$objPHPExcel->getActiveSheet()->setCellValue('B'.($row_index),'总计');
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF90b3bc');
		$row_index++;
		
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	//月仓储或装卸统计输出excel
	private function toStorage_statistics_Excel($models,$sum_model){
		
		$model=$models[0];
		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$tab_name=$_POST['s_date'].($_POST['b_type']==1?'仓储':'装卸')."业务报表";
				
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		//所有单元格添加边框
		$sum=count($models);
		$sharedStyle = new PHPExcel_Style();
		$sharedStyle->applyFromArray(
		array('borders' => array(
			'left'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
			)
		 ));
		$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle, "A1:j".($sum+3));
		
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$end_cell[0].'1');
		$objPHPExcel->getActiveSheet()->setCellValue('A1',$tab_name);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(15);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->setCellValue('A2','序号');
		$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
		$objPHPExcel->getActiveSheet()->freezePane('A3');//标题栏 冻结窗口
		foreach($excel_names as $attr=>$val){//创建标题
			$objPHPExcel->getActiveSheet()->setCellValue($val[0].'2',$atrr_names[$attr]);
			$objPHPExcel->getActiveSheet()->getColumnDimension($val[0])->setWidth($val[1]);
		}
		$row_index=3;//行索引 数据从3开始 例如A从A3开始添加数据
		//仓储或装卸遍历
		foreach($models as $n=>$model){
			$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
			foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
				$value=$model->$attr;
				if($attr=='disparity_sum') $value=sprintf('%.2f',$model->disparity/$sum_model->disparity*100).'%';
				$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
				
			}
			if($n%2==1)
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFefefef');
			$row_index++;
		}
		//总计
		$model=$sum_model;
		$objPHPExcel->getActiveSheet()->setCellValue('A'.($row_index),$row_index-2);
		foreach($excel_names as $attr=>$val){//双循环，根据model内设置进行数据导出
			$value=$model->$attr;
			if($attr=='disparity_sum') $value='100%';
			else if($attr=='disparity_ratio') $value=sprintf('%.2f',($model->disparity)/($model->receivable_amount)*100).'%';
			$sheet=$objPHPExcel->getActiveSheet()->setCellValue($val[0].($row_index),$value);
		}
		$objPHPExcel->getActiveSheet()->setCellValue('B'.($row_index),'总计');
		$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':J'.$row_index)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF90b3bc');
		$row_index++;
		
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output'); 
	}
	
	//年统计
	public function actionAnnual_statistics(){
		if(Yii::app()->request->isPostRequest){
			if(trim($_POST['s_date'])=='') $_POST['s_date']=date('Y');
			$model=Business_detail::model();
			$where='';//如果运输判断承运方
			if($_POST['b_type']==0&&$_POST['c_type']!=2){ 
				$where=" and carrier_type={$_POST['c_type']}";
			}
			$sql="select customer_name.name as customer_name,sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,carrier_type,date_format(occur_time,'%m') as occur_time
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type={$_POST['b_type']} and date_format(occur_time,'%Y')='{$_POST['s_date']}' and business_detail.state=0".$where."
					GROUP BY fk_customer_id,date_format(occur_time,'%Y-%m')
					order by fk_customer_id,date_format(occur_time,'%Y-%m')";
			$models=$model->findAllBySql($sql);//各客户数据
			if(count($models)==0){//如果承运为空
				if(isset($_GET['excel']))//输出excel
					$this->redirect(array('site/to_close','message'=>'4'));
				else
					$this->render('annual_statistics');
				return;
			}
			$customer_models=array();
			foreach($models as $n=>$model){
				if(!isset($customer_models[$model->customer_name])) $customer_models[$model->customer_name]=array();
				$customer_models[$model->customer_name][$model->occur_time]=$model;
			}
			$sql="select customer_name.name as customer_name,sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,carrier_type,date_format(occur_time,'%m') as occur_time
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type={$_POST['b_type']} and date_format(occur_time,'%Y')='{$_POST['s_date']}' and business_detail.state=0".$where."
					GROUP BY fk_customer_id
					order by fk_customer_id";
			$sum_models=$model->findAllBySql($sql);//列合计
			$sql="select customer_name.name as customer_name,sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,carrier_type,date_format(occur_time,'%m') as occur_time
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type={$_POST['b_type']} and date_format(occur_time,'%Y')='{$_POST['s_date']}' and business_detail.state=0".$where."
					GROUP BY date_format(occur_time,'%Y-%m')
					order by date_format(occur_time,'%Y-%m')";
			$models=$model->findAllBySql($sql);//行合计
			foreach($models as $n=>$model){
				$sum1_models[$model->occur_time]=$model;
			}
			$sql="select customer_name.name as customer_name,sum(truck_times) as truck_times, sum(tonnage) as tonnage, sum(receivable_amount) as receivable_amount,
					sum(payable_amount) as payable_amount,sum(disparity) as disparity, CONCAT(FORMAT(sum(disparity)/sum(receivable_amount)*100,2),'%') as disparity_ratio,
					sum(loss_amount) as loss_amount,carrier_type
					from business_detail
					INNER JOIN customer_name on business_detail.fk_customer_id=customer_name.id
					where business_detail.business_type={$_POST['b_type']} and date_format(occur_time,'%Y')='{$_POST['s_date']}' and business_detail.state=0".$where;
			$sum2_models=$model->findBySql($sql);//总合计
			if(isset($_GET['excel']))//输出excel
				$this->toAnnual_statistics_Excel($customer_models,$sum_models,$sum1_models,$sum2_models);
			else
				$this->render('annual_statistics',
					array('customer_models'=>$customer_models,
					'sum_models'=>$sum_models,
					'sum1_models'=>$sum1_models,
					'sum2_models'=>$sum2_models,
				));
			return;
		}
		if(isset($_GET['excel']))//输出excel
			$this->redirect(array('site/to_close','message'=>'4'));
		else
			$this->render('annual_statistics');
		return;	
	}
	
	//年统计输出excel
	private function toAnnual_statistics_Excel($customer_models,$sum_models,$sum1_models,$sum2_models){
		$model=new Business_detail;
		$atrr_names=$model->attributeLabels();
		$excel_names=$model->excelAttributes();$end_cell=end($excel_names);
		$t_name=array('运输','仓储','装卸');
		$tab_name=$_POST['s_date'].'年'.$t_name[$_POST['b_type']]."业务报表";
		if($_POST['b_type']==0&&$_POST['c_type']!=2){ 
			$tab_name.=$_POST['c_type']==0?'(自有车)':'(第三方)';
		}
				
		require_once './protected/extensions/excel/PHPExcel.php';
		require_once './protected/extensions/excel/PHPExcel/IOFactory.php';
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->setActiveSheetIndex(0)->setTitle($tab_name);
		$sheet=$objPHPExcel->getActiveSheet();
		
		//所有单元格添加边框
		$sum=count($customer_models);
		$sharedStyle = new PHPExcel_Style();
		$sharedStyle->applyFromArray(
		array('borders' => array(
			'left'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'top'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'bottom'	=> array('style' => PHPExcel_Style_Border::BORDER_THIN),
			'right'		=> array('style' => PHPExcel_Style_Border::BORDER_THIN)
			)
		 ));
		$sheet->setSharedStyle($sharedStyle, "A1:O".($sum*6+8));
		//开始设置标题
		$sheet->mergeCells('A1:O1');
		$sheet->setCellValue('A1',$tab_name);
		$sheet->getStyle('A1')->getFont()->setSize(15);
		$sheet->getStyle('A1')->getAlignment()->setHorizontal('center');
		$sheet->mergeCells('A2:B2');
		$sheet->setCellValue('A2','客户名称');
		$sheet->getStyle('A2')->getAlignment()->setHorizontal('center');
		$sheet->getColumnDimension('A')->setWidth(15);
		$sheet->freezePane('C3');//标题栏 冻结窗口
		$t=array('C','D','E','F','G','H','I','J','K','L','M','N');
		for($i=0;$i<12;$i++){
			$sheet->setCellValue($t[$i].'2',($i+1).'月');
			$sheet->getColumnDimension($t[$i])->setWidth(12);
			$sheet->getStyle($t[$i].'2')->getAlignment()->setHorizontal('center');
		}
		$sheet->setCellValue('O2','累计');
		$sheet->getColumnDimension('O')->setWidth(12);
		$sheet->getStyle('O2')->getAlignment()->setHorizontal('center');
		$row_index=3;
		$month=array('01','02','03','04','05','06','07','08','09','10','11','12');
		$sum_index=0;
		foreach($customer_models as $name=>$customer){//遍历客户
			$sheet->mergeCells('A'.$row_index.':A'.($row_index+5));
			$sheet->setCellValue('A'.$row_index,$name);
			$sheet->getStyle('A'.$row_index)->getAlignment()->setVertical('center')->setWrapText(true);
			$title_names=$model->excelAnnualAttributes();
			foreach($title_names as $n=>$title){//插入小标题 车次 吨位....
				$sheet->setCellValue('B'.($row_index+$n),$atrr_names[$title]);
				$sheet->getStyle('B'.($row_index+$n))->getFont()->setSize(10);
			}
			foreach($month as $m){//按月循环
				if(isset($customer[$m])){
					foreach($title_names as $n=>$title){
						$sheet->setCellValue($t[$m-1].($row_index+$n),$customer[$m]->$title);
					}
				} 
			}
			foreach($title_names as $n=>$title){//列合计
				$sheet->setCellValue('O'.($row_index+$n),$sum_models[$sum_index]->$title);
			}
			if($sum_index%2)//每隔一行背景颜色
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':O'.($row_index+5))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFefefef');
			$sum_index++;
			$row_index+=6;
		}	
		//开始总计
		$sheet->mergeCells('A'.$row_index.':A'.($row_index+5));
		$sheet->setCellValue('A'.$row_index,'总计');
		$sheet->getStyle('A'.$row_index)->getAlignment()->setVertical('center')->setWrapText(true);
		foreach($title_names as $n=>$title){//插入小标题 车次 吨位....
			$sheet->setCellValue('B'.($row_index+$n),$atrr_names[$title]);
			$sheet->getStyle('B'.($row_index+$n))->getFont()->setSize(10);
		}
		foreach($month as $m){//按月循环
			if(isset($sum1_models[$m]))
				foreach($title_names as $n=>$title){
					$sheet->setCellValue($t[$m-1].($row_index+$n),$sum1_models[$m]->$title);
				}
		}
		//开始总合计
		foreach($title_names as $n=>$title){//列合计
			$sheet->setCellValue('O'.($row_index+$n),$sum2_models->$title);
			if($sum_index%2)//每隔一行背景颜色
				$objPHPExcel->getActiveSheet()->getStyle('A'.$row_index.':O'.($row_index+5))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFefefef');
		}
		
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename='.iconv("utf-8", "gb2312", $tab_name.'.xls'));
		header('Cache-Control: max-age=0');
		$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
	}
}
